#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *

import sys
import datetime
reload(sys)
sys.setdefaultencoding('utf-8')

#mysql - stats
def set_stats_conn():    
    conn = HandleMysql('10.3.10.50','58885','ec_stat','L#c8UM5RGh5C','dbwww58com_groupbuy_stats')
    conn.connect()
    return conn

#mysql - infobright
def set_ib_conn():    
    conn = HandleMysql('10.3.13.27','5029','root','group_buytj1140','groupbuy')
    conn.connect()
    return conn

#mysql - cs
def set_cs_conn():    
    conn = HandleMysql('192.168.10.81','58885','user_customer',"l@#jsaf(*safd1",'dbwww58com_groupbuy_customer')
    conn.connect()
    return conn

#task stats
def set_task_stats(date):
    conn = set_cs_conn()
    query = "SELECT count(1) FROM `c_task` where date(create_time)='%s' and task_type<5" %date
    items = conn.get_results(query)
    cnt = items[0][0]
    
    #print cnt
    
    query_first = "SELECT count(1) FROM `c_task` where date(end_time)='%s'"%date
    items_first = conn.get_results(query_first)
    cnt_first = items_first[0][0]
    
    #print cnt_first
    
    query_second = "SELECT count(1) FROM `c_workorder` where date(endtime)='%s'"%date
    items_second = conn.get_results(query_second)
    cnt_second = items_second[0][0]
    
    query_today_first = "SELECT count(1),sum(case when  date(end_time)='%s' then 1 end) FROM `c_task` where date(create_time)='%s' "%(date,date)
    items_today_first = conn.get_results(query_today_first)
    cnt_today_add_first = items_today_first[0][0]
    cnt_today_first = items_today_first[0][1]
    
    query_today_second = "SELECT count(1),sum(case when  date(endtime)='%s' then 1 end) FROM `c_workorder` where date(submittime)='%s'"%(date,date)
    items_today_second = conn.get_results(query_today_second)
    cnt_today_add_second = items_today_second[0][0]
    cnt_today_second = items_today_second[0][1]
    
    #print cnt_second
    
    cnt_done = cnt_first + cnt_second
    
    cnt_today_done = cnt_today_first + cnt_today_second
    cnt_today_add = cnt_today_add_first + cnt_today_add_second
    today_done_rate = 0
    
    if cnt_today_add > 0:
        today_done_rate = cnt_today_done/cnt_today_add

    

    twoday = datetime.timedelta(days=2)    
    tmp = time.strptime(date,"%Y-%m-%d")
    
    be_yestoday = datetime.datetime(tmp[0],tmp[1],tmp[2])  - twoday
    
    query_add = "SELECT count(1) FROM `c_task` where date(create_time)='%s'"%(be_yestoday)
    items_add = conn.get_results(query_add)
    cnt_48add_task = items_add[0][0]
    #print cnt_48add_task
    
    query_add_order = "SELECT count(1) FROM `c_workorder` where date(submittime)='%s'"%(be_yestoday)
    #print query_add
    items_add_order = conn.get_results(query_add_order)
    cnt_48add_order = items_add_order[0][0]
    
    #print cnt_48add
    cnt_48add = cnt_48add_task + cnt_48add_order
    
    query_done = "SELECT count(1) FROM `c_task` where date(create_time)='%s' and end_time>'%s' and end_time<'%s'"%(be_yestoday,be_yestoday,date)
    items_done = conn.get_results(query_done)
    cnt_48done_task = items_done[0][0]
    
    query_done_order = "SELECT count(1) FROM `c_workorder` where date(submittime)='%s' and endtime>'%s' and endtime<'%s'"%(be_yestoday,be_yestoday,date)
    items_done_order = conn.get_results(query_done_order)
    cnt_48add_order = items_done_order[0][0]

    cnt_48done = cnt_48done_task + cnt_48add_order
    
    rate = cnt_48done/(cnt_48add+0.0)
    #print rate
    st_conn = set_stats_conn()
    st_query = "select count(1) from cs_task_stats where stat_date='%s'"%date
    items_st = st_conn.get_results(st_query)
    cnt_st = items_st[0][0]
    if(cnt_st > 0):
        pm = (cnt,cnt_first,cnt_second,cnt_done,cnt_today_add,cnt_today_done,today_done_rate,cnt_48add,cnt_48done,rate,date)
        update_sql = "update cs_task_stats set rev_count=%s,fir_done_count=%s,sec_done_count=%s,all_done_count=%s,today_add=%s,today_done=%s,today_rate=%s,add_in48hour=%s,done_in48hour=%s,done_rate=%s where stat_date='%s'"%pm
        st_conn.execute_sql(update_sql)
    else:
        param = [date,cnt,cnt_first,cnt_second,cnt_done,cnt_today_add,cnt_today_done,today_done_rate,cnt_48add,cnt_48done,rate]
        stat_table = "cs_task_stats"  
        st_conn.insert_record(stat_table,param)
    #print cnt_48done
    conn.close()
    st_conn.close()
    
#user first task stats
def set_firsttask_stats(date):
    conn = set_cs_conn()
    query = "SELECT a.cs_userid,b.username,b.realname,b.job,a.task_type,a.wid,count(1) FROM `c_task` a left join c_user b on a.cs_userid=b.id where date(create_time)='%s' and a.state=3 and task_type in (1,2,5) group by a.cs_userid,a.task_type,a.wid" %date
    items = conn.get_results(query)
    
    stats_conn = set_stats_conn();
    query_temp_delete = "delete from cs_usertask_temp where stat_date='%s'"%date
    stats_conn.execute_sql(query_temp_delete)
    #insert tmp
    for item in items:
        cs_userid = item[0]
        user_name = item[1]
        real_name = item[2]
        user_type = item[3]
        task_type = item[4]
        wid = item[5]
        if wid > 0:
            wid = 1
            
        cnt = item[6]
        
        #print cnt
        
        param = [date,cs_userid,user_type,user_name,real_name,task_type,cnt,wid]
        stat_table = "cs_usertask_temp"  
        stats_conn.insert_record(stat_table,param)
        
        
    query_stats_delete = "delete from cs_firsttask_stats where stat_date='%s'"%date
    stats_conn.execute_sql(query_stats_delete)
    #print 'do delete'
    query_stats_insert = "insert cs_firsttask_stats(stat_date,cs_userid,cs_user_type,cs_userName,cs_realName,question_count,hotline_count,msg_count,reject_count,reject_rate,submit_count,submit_rate) select stat_date,cs_userid,cs_user_type,cs_userName,cs_realName, sum(case when task_type<3 then done_count else 0 end) as question, sum(case when task_type=1 then done_count else 0 end) as hotline, sum(case when task_type=2 then done_count else 0 end) as msg,sum(case when task_type=5 then done_count else 0 end) as reject,sum(case when task_type=5 then done_count else 0 end)/count(case when wid=1 then 1 end),count(case when wid=1 then 1 end) as submit,count(case when wid=1 then 1 end)/sum(done_count)  FROM `cs_usertask_temp` where stat_date='%s' GROUP BY stat_date,cs_userid,cs_user_type;"%date
    stats_conn.execute_sql(query_stats_insert)
    
    query_tmp = "select min(id),max(id) from c_task where date(create_time)='%s'"%date
    items_tmp = conn.get_results(query_tmp)
    minid = items_tmp[0][0]
    maxid = items_tmp[0][1]
    
    query_oneday = "select cs_userid,count(1) FROM `c_task` where id>= %s and id<=%s and state>2 and date(end_time)='%s'  group by cs_userid" %(minid,maxid,date)
    #print query_oneday
    items_oneday = conn.get_results(query_oneday)
    for item in items_oneday:
        cs_userid = item[0]
        cnt = item[1]
        
        update_sql = "update cs_firsttask_stats set done_oneday=%s,done_oneday_rate=%s/question_count where stat_date='%s' and cs_userid=%s"%(cnt,cnt,date,cs_userid)
        #print update_sql
        stats_conn.execute_sql(update_sql)
        
    #second
    
        
    #TODO RING_DATA
    ib_conn = set_ib_conn()
    query_ib_call = "SELECT agent,count(1), count(case when state='dealing' then 1 end) FROM `call_ring` where date(ring_time)='%s' GROUP BY agent"%date
    #print query_ib_call
    items_call = ib_conn.get_results(query_ib_call)
    for item in items_call:
        agent = item[0]
        #print agent
        call_cnt = item[1]
        comm_cnt = item[2]
        if(agent != None):
            update_sql = "update cs_firsttask_stats set comm_count=%s,call_count=%s,log_rate=hotline_count/%s,comm_rate=%s/%s where stat_date='%s' and cs_userName='%s'"%(comm_cnt,call_cnt,comm_cnt,comm_cnt,call_cnt,date,agent)
            #print update_sql
            stats_conn.execute_sql(update_sql)

    conn.close()
    stats_conn.close()
    print "OK"
    #pass

#second stats
def set_secondtask_stats(date):
    conn = set_cs_conn()
    stats_conn = set_stats_conn();
    
    #delete old data
    query_delete = "delete from cs_secondtask_stats where stat_date='%s'"%date
    stats_conn.execute_sql(query_delete)
    
    query = "SELECT a.accepted,b.realname,b.job,count(case when a.type=0 then 0 end) as normal,count(case when a.type=1 then 1 end) as logistics,count(1) as order_count,count(case when date(a.submittime)='%s' then 1 end) as today_done FROM `c_workorder` a left join  c_user b on a.accepted=b.id  where a.accepted>0 and date(a.endtime)='%s'  group by date(a.endtime),accepted;" %(date,date)
    items = conn.get_results(query)
    for item in items:
        cs_userid = item[0]
        cs_username=item[1]
        cs_user_type=item[2]
        normal = item[3]
        lgoistics = item[4]
        order_count=item[5]
        today_done = item[6]
        
        q_cnt = normal + lgoistics
        
        query_st = "SELECT count(1) FROM `c_workorder`  where accepted=%s and date(submittime)='%s' " %(cs_userid,date)
        items_st = conn.get_results(query_st)
        today_add = items_st[0][0]
        
        today_rate = 0
        if today_add>0:
            today_rate = today_done/today_add
        
        insert_sql = "insert into cs_secondtask_stats(stat_date,cs_userId,cs_user_type,cs_userName,question_count,order_count,normal_count,logistics_count,today_add,today_done,today_rate) values('%s',%s,%s,'%s',%s,%s,%s,%s,%s,%s,%s) "%(date,cs_userid,cs_user_type,cs_username,q_cnt,order_count,normal,lgoistics,today_add,today_done,today_rate)
        #print insert_sql
        stats_conn.execute_sql(insert_sql)
    
    query_newtask = "SELECT accepted,count(1) FROM `c_workorder` where date(submittime)='%s' group by accepted"%date
    items_newtask = conn.get_results(query_newtask)
    for item in items_newtask:
        cs_userid = item[0]
        cnt = item[1]
        update_sql = "update cs_secondtask_stats set question_count=question_count+%s,new_task_count=%s where stat_date='%s' and cs_userid=%s"%(cnt,cnt,date,cs_userid)
        stats_conn.execute_sql(update_sql)
        
    query_disp = "SELECT dealingid, count(case when source=0 then 0 end) as jiaofen ,count(case when source=1 then 1 end) as weiquan FROM `c_dispute` where date(endtime)='%s' group by dealingid"%date
    items_disp = conn.get_results(query_disp)
    for item in items_disp:
        cs_userid = item[0]
        dispute = item[1]
        rights = item[2]
        
        q_cnt = dispute + 1
        update_sql = "update cs_secondtask_stats set question_count=question_count+%s,dispute_count=%s,rights_count=%s where stat_date='%s' and cs_userid=%s"%(q_cnt,dispute,rights,date,cs_userid)
        stats_conn.execute_sql(update_sql)
    
    #oneday = datetime.timedelta(days=2)
    twoday = datetime.timedelta(days=2)
    tmp = time.strptime(date,"%Y-%m-%d")
     
    #yestoday = datetime.datetime(tmp[0],tmp[1],tmp[2])  - oneday    
    be_yestoday = datetime.datetime(tmp[0],tmp[1],tmp[2])  - twoday
    
    query_add = "SELECT accepted,count(1) FROM `c_workorder` where date(submittime)='%s' group by accepted"%(be_yestoday)
    #print query_add
    items_add = conn.get_results(query_add)
    for item in items_add:
        cs_userid = item[0]
        #print cs_userid
        cnt_48add = item[1]
        update_sql = "update cs_secondtask_stats set add_in48hour=%s where stat_date='%s' and cs_userid=%s "%(cnt_48add,date,cs_userid)
        stats_conn.execute_sql(update_sql)
    
    #print cnt_48add
    
    query_done = "SELECT accepted,count(1) FROM `c_workorder` where date(submittime)='%s' and endtime>'%s' and endtime<'%s' group by accepted"%(be_yestoday,be_yestoday,date)
    #print query_done
    items_done = conn.get_results(query_done)
    for item in items_done:
        cs_userid = item[0]
        cnt_48done = item[1]
        update_sql = "update cs_secondtask_stats set done_in48hour=%s,done_rate=%s/add_in48hour where  stat_date='%s' and cs_userid=%s "%(cnt_48done,cnt_48done,date,cs_userid)
        stats_conn.execute_sql(update_sql)
    
    #pass
    conn.close()
    stats_conn.close()
#logic
def execute(date):

    try:
        #pass
        set_task_stats(date)
    except Exception,e:
        print 'set_task_stats:',e
    try:
        #pass
        set_firsttask_stats(date)
    except Exception,e:
        print 'set_firsttask_stats:',e
    try:
        #pass
        set_secondtask_stats(date)
    except Exception,e:
        print 'set_secondtask_stats:',e
#
def main():

   #date = '2012-05-28'
   #execute(date)   
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
   stat_date=start_date
   while stat_date>=start_date and stat_date<=end_date:
        print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
        #
        execute( stat_date) 
        print "The program of the date of %s is end at %s " %(stat_date,get_now())
        stat_date=var_date(stat_date)


if __name__=='__main__':
    main()
